import os
import sqlite3
import threading
from config.Common import get_sqlite_db_path
from config.ServerApp import app
from utils.Singleton import Singleton


# 创建一个线程安全的连接池
@Singleton
class SQLiteConnectionPool:
    def __init__(self, db_path, max_connections=5):
        if hasattr(self, 'initialized'):
            return
        self.initialized = True
        self.db_path = db_path
        self.max_connections = max_connections
        self.pool = threading.local()
        self.lock = threading.Lock()

    def get_connection(self):
        if not hasattr(self.pool, 'connection') or self.pool.connection is None:
            with self.lock:
                if not hasattr(self.pool, 'connection') or self.pool.connection is None:
                    # 检查数据库文件路径是否存在，如果不存在则创建目录
                    if not os.path.exists(os.path.dirname(self.db_path)):
                        os.makedirs(os.path.dirname(self.db_path))
                    app.ctx.logger.info(f"Connecting to database at: {self.db_path}")
                    # 设置超时时间为 10 秒
                    self.pool.connection = sqlite3.connect(self.db_path, timeout=30)
                    # 设置 WAL 模式
                    self.pool.connection.execute('PRAGMA journal_mode=WAL;')
                    self.pool.connection.commit()
        return self.pool.connection

    def release_connection(self, conn):
        with self.lock:
            if self.pool.connection == conn:
                self.pool.connection.close()
                self.pool.connection = None

# 创建连接池实例
pool = SQLiteConnectionPool(get_sqlite_db_path())

def sqlite_execute_sql(sql: str, params: tuple = None):
    conn = pool.get_connection()
    cursor = conn.cursor()
    try:
        app.ctx.logger.info(f"执行SQL: {sql}, params: {params}")
        if params is None:
            cursor.execute(sql)
        else:
            cursor.execute(sql, params)
        conn.commit()
        last_row_id = cursor.lastrowid
        return last_row_id
    except Exception as e:
        app.ctx.logger.error(f"执行sql: {sql} 失败: {str(e)}")
        conn.rollback()
        raise e
    finally:
        cursor.close()

def sqlite_batch_insert_data(insert_sql: str, data_list: list):
    conn = pool.get_connection()
    cursor = conn.cursor()
    try:
        app.ctx.logger.info(f"执行批量插入SQL: {insert_sql}, params: {data_list}")
        cursor.executemany(insert_sql, data_list)
        conn.commit()
    except Exception as e:
        app.ctx.logger.error(f"执行sql: {insert_sql} 失败: {str(e)}")
        conn.rollback()
        raise e
    finally:
        cursor.close()

def sqlite_update_sql(update_sql: str, params: tuple = None):
    conn = pool.get_connection()
    cursor = conn.cursor()
    try:
        app.ctx.logger.info(f"执行更新SQL: {update_sql}, params: {params}")
        if params is None:
            cursor.execute(update_sql)
        else:
            cursor.execute(update_sql, params)
        conn.commit()
        return cursor.rowcount
    except Exception as e:
        app.ctx.logger.error(f"执行sql: {update_sql} 失败: {str(e)}")
        conn.rollback()
        raise e
    finally:
        cursor.close()

def sqlite_execute_query(sql: str, params: tuple):
    conn = pool.get_connection()
    cursor = conn.cursor()
    try:
        app.ctx.logger.info(f"执行查询SQL: {sql}, params: {params}")
        if params is None:
            cursor.execute(sql)
        else:
            cursor.execute(sql, params)
        data_list = cursor.fetchall()
        field_names = [description[0] for description in cursor.description]
        return [dict(zip(field_names, row)) for row in data_list]
    except Exception as e:
        app.ctx.logger.error(f"执行sql: {sql} 失败: {str(e)}")
        conn.rollback()
        raise e
    finally:
        cursor.close()

# 释放连接
def release_connection():
    conn = pool.get_connection()
    pool.release_connection(conn)

# just for test
if __name__ == '__main__':
    # 插入测试数据的SQL语句
    insert_sql = '''
    INSERT INTO process_task_record (batch_id, gmt_create, gmt_modify, task_status, remark)
    VALUES (?, ?, ?, ?, ?)
    '''

    # 测试数据
    test_data = [
        ('123', '2024-06-11 10:00:00', '2024-06-11 10:00:00', 'pending', 'First task'),
        ('123', '2024-06-11 10:05:00', '2024-06-11 10:05:00', 'completed', 'Second task'),
        ('123', '2024-06-11 10:10:00', '2024-06-11 10:10:00', 'failed', 'Third task'),
    ]
    data = ('testBatchId01', '2024-06-11 10:10:00', '2024-06-11 10:10:00', 'failed', 'Fourth task')
    id = sqlite_execute_sql(insert_sql, data)
    print(f"======>{id}")

    # 批量插入数据
    sqlite_batch_insert_data(insert_sql, test_data)

    # 查询测试
    query_sql = '''
    SELECT * FROM process_task_record
    WHERE task_status = ? AND batch_id = ?
    '''
    result = sqlite_execute_query(query_sql, ('completed', '123'))
    print(result)

    # 释放连接
    release_connection()
